SportsStats Olympic Athletes Analysis

import pandas as pd

import matplotlib.pyplot as plt

# Load your Olympic athlete data
df_olympics = pd.read_csv("athlete_events.csv")
noc_df = pd.read_csv("noc_regions.csv")

df_olympics.info()

# 1. Remove rows with null in critical columns
df_olympics = df_olympics.dropna(subset=['Name', 'Sex', 'Age', 'Height', 'Weight', 'NOC', 'Sport', 'Event'])

# 2: Remove duplicate rows
df_olympics = df_olympics.drop_duplicates()

# 3. Convert data types
df_olympics['Age'] = df_olympics['Age'].astype(int)
df_olympics['Height'] = df_olympics['Height'].astype(int)
df_olympics['Weight'] = df_olympics['Weight'].astype(int)

# 4. Join with noc_regions.csv on 'NOC'
df_merged = df_olympics.merge(noc_df, on='NOC', how='left')

# 5. Fill missing region values (if needed)
df_merged['region'] = df_merged['region'].fillna('Unknown')

# Total records
print("Total Records:", df_merged.shape[0])

# Unique countries
print("Total Countries:", df_merged['region'].nunique())

# Top 5 Medal-Winning Countries
print("\nTop Countries by Medal Count:")
print(df_merged[df_merged['Medal'].notna()]['region'].value_counts().head(5))

# Male vs Female participation
print("\nGender Participation:")
print(df_merged['Sex'].value_counts())

import duckdb

query = """
    SELECT region, COUNT(*) AS total_medals
    FROM 'olympics_cleaned.csv'
    WHERE medal IS NOT NULL
    GROUP BY region
    ORDER BY total_medals DESC
    LIMIT 10
"""

total_medals = duckdb.query(query).df()
print(total_medals)

# Plot Top 10 Countries by Total Medals
plt.figure(figsize=(10,6))
plt.bar(df_medals['region'].head(10), df_medals['total_medals'].head(10), color='steelblue')
plt.title('Top 10 Countries by Total Medals')
plt.ylabel('Total Medals')
plt.xlabel('Country')
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

gender_participation = duckdb.query("""
    SELECT 
        year, 
        sex, 
        COUNT(DISTINCT id) AS athlete_count
    FROM 'olympics_cleaned.csv'
    GROUP BY year, sex
    ORDER BY year, sex
""").df()

male_data = gender_participation[gender_participation['Sex'] == 'M']
female_data = gender_participation[gender_participation['Sex'] == 'F']

plt.figure(figsize=(12, 6))
plt.plot(male_data['Year'], male_data['athlete_count'], label='Male', color='blue')
plt.plot(female_data['Year'], female_data['athlete_count'], label='Female', color='red')
plt.title('Male vs Female Athlete Participation Over Time')
plt.xlabel('Year')
plt.ylabel('Number of Athletes')
plt.legend()
plt.show()

query = """
SELECT 
    Sport,
    COUNT(*) AS total_athletes,
    AVG(Age) AS avg_age,
    AVG(Height) AS avg_height,
    AVG(Weight) AS avg_weight
FROM "olympics_cleaned.csv"
WHERE Height IS NOT NULL AND Weight IS NOT NULL AND Age IS NOT NULL
GROUP BY Sport
ORDER BY avg_height DESC
"""

Physical_profiles_by_sport = duckdb.query(query).df()
print(Physical_profiles_by_sport.head(10))

query = """
SELECT 
    Sport,
    AVG(Age) AS avg_age
FROM "olympics_cleaned.csv"
WHERE Medal IS NOT NULL
  AND Sport IN ('Rhythmic Gymnastics', 'Equestrianism')
GROUP BY Sport
"""

Avg_Age_by_Sport = duckdb.query(query).df()
print(Avg_Age_by_Sport)

query = """
SELECT 
    region,
    COUNT(DISTINCT ID) AS total_athletes,
    COUNT(Medal) AS total_medals,
    ROUND(COUNT(Medal) * 1.0 / COUNT(DISTINCT ID), 2) AS medal_efficiency
FROM 'olympics_cleaned.csv'
WHERE Medal IS NOT NULL
GROUP BY region
HAVING total_athletes > 0
ORDER BY medal_efficiency DESC
"""

medal_efficiency_per_athletes = duckdb.query(query).df()
print(medal_efficiency_per_athletes.head(10))